﻿/*
 * 
 *	static class of DB-related utility functions; the general core logic of dynamic pages
 *	TODO: lots
 * 
 */

using System;
using System.Linq;
using System.Collections.Generic;
using System.Data;
using MySql.Data.MySqlClient;
using ResearchRepository.Models;

namespace eoTest
{
    public static class RRDatabaseManager
    {
		// connection used for lifetime of RRDatabaseManager/application
			// conn is opened and closed on each call to ExecuteNonQuery and ExecuteScalar
			// conn is only opened by ExecuteReader and then closed by GetData after all data is read from the reader (conn must be open to read)
			// if GetData is not used to read the results of ExecuteReader, then it is the reading function's responsibility to close conn
		public static readonly MySqlConnection conn = new MySqlConnection(GLOBALS.dbConnStr);
		
		/*
        public void Dispose()
        {
            if (conn.State != ConnectionState.Closed)
            {
                conn.Close();
            }
			conn.Dispose();
        }
		*/

		public static string ConnectionString
		{
			get {return conn.ConnectionString;}
			set	{conn.ConnectionString = value;}
		}

		public static int ExecuteNonQuery(string sqlStr)
		{
			try
			{
				MySqlCommand cmd = new MySqlCommand(sqlStr, conn);
				if (conn.State == ConnectionState.Closed)
				{
					conn.Open();
				}
				return cmd.ExecuteNonQuery();
			}
			catch (Exception x)
			{
				//TODO: handle exception
				throw x;
				//return -1;
			}
			finally
			{
				conn.Close();
			}
		}
		public static object ExecuteScalar(string sqlStr)
		{
			try
			{
				MySqlCommand cmd = new MySqlCommand(sqlStr, conn);
				if (conn.State == ConnectionState.Closed)
				{
					conn.Open();
				}
				return cmd.ExecuteScalar();
			}
			catch (Exception x)
			{
				//TODO: handle exception
				throw x;
				//return null;
			}
			finally
			{
				conn.Close();
			}
		}
		public static MySqlDataReader ExecuteReader(string sqlStr)
		{
			try
			{
				MySqlCommand cmd = new MySqlCommand(sqlStr, conn);
				if (conn.State == ConnectionState.Closed)
				{
					conn.Open();
				}
				//return cmd.ExecuteReader();
				MySqlDataReader r = cmd.ExecuteReader();
				return r;
			}
			catch (Exception x)
			{
				//TODO: handle exception
				throw x;
				//return null;
			}
			finally
			{
				//conn.Close();
			}
		}
		
		// validate credentials or check for existing user
			// calling function is responsible for handling exceptions
		public static RRUser getUser(string username, string password = null)
		{
			string sqlStr = String.Format("SELECT * FROM users WHERE userName='{0}' {1}",
											username, password == null ? "" : String.Format("AND userPass=PASSWORD('{0}')", password));
			List<RRUser> res = new List<RRUser>(GetData(ExecuteReader(sqlStr), RRUser.construct));
			return res.Count == 1 ? res[0] : null;
			//TODO?: worry about how to handle count > 1?? should be covered by schema constraints
		}

		// get updated data for file and user
			// if file is no longer accessible (deleted/moved/permission revoked), then return null
			// else return new file data, with sub files populated down to the depth passed (0 = just get data for given file)
		//TODO?: exception handling??
		public static RRFile getFileData(RRFile file, int userID, int depth = 0)
		{
			// if file is repo root (fileId==-1) then use default root as super for base permissions
			RRFile superDir = RRFile.EmptyRoot;

			// else: validate old parent dir permissions
			if (file.FileID != -1)
			{
				// recursively get up-to-date data for super
				superDir = getFileData(file.SuperDir, userID);
				// if parent is unreachable, then so is this file
				if (superDir == null)
				{
					return null;
				}
			}
			// build MySQL query to update starting file
			string sqlStr = "SELECT f.*, " + (userID==RRUser.GuestUserID ? "NULL"								// if guest user, then don't look for user-specific permissions
												: superDir.UserPermission==RRPermissionType.admin ? "'admin'"	// elseif registered user has 'admin' permission on parent dir, then all children have 'admin' permission
																												// else, find registered user's permissions for children:
												: "(SELECT CASE WHEN EXISTS (SELECT * FROM permissions p WHERE p.userID=" + userID + " AND p.fileID=f.fileID)"														// if there is an explicit permission
																+ " THEN (SELECT pt.permName FROM permissions p, permissionTypes pt WHERE p.userID=" + userID + " AND p.fileID=f.fileID AND p.permID=pt.permID)"	// then get that permission
																+ " ELSE '" + superDir.UserPermission.ToString() + "' END)")																						// else, inherit permission from parent
											+ " AS userPermName, "
											+ (superDir.PublicPermission==RRPermissionType.none ? "'none'"		// if public has 'none' permission on parent dir, then all children have 'none' permission
												: superDir.PublicPermission==RRPermissionType.admin ? "'admin'"	// elseif public has 'admin' permission on parent dir, then all children have 'admin' permission (this is obviously not suggested, but allowable)
																												// else, find public's permissions for children:
												: "(SELECT CASE WHEN EXISTS (SELECT * FROM permissions p WHERE p.userID=(SELECT userID FROM users WHERE userName='guest') AND p.fileID=f.fileID)"													// if there is an explicit permission
																+ " THEN (SELECT pt.permName FROM permissions p, permissionTypes pt WHERE p.userID=(SELECT userID FROM users WHERE userName='guest') AND p.fileID=f.fileID AND p.permID=pt.permID)"	// then get that permission
																+ " ELSE '" + superDir.PublicPermission.ToString() + "' END)")																														// else, inherit permission from parent
											+ " AS publicPermName"
									+ " FROM files f WHERE f.fileID=" + file.FileID				// just get starting file
									+ " HAVING userPermName!='none' OR publicPermName!='none'";	// a file is only reachable if user has some permission
			// run query
			List<RRFile> res = new List<RRFile>(GetData(ExecuteReader(sqlStr), RRFile.construct));
			// if empty result, then file is no longer accessible
			if (res.Count == 0)
			{
				return null;
			}
			// else get updated file data
			RRFile newFile = res.ElementAt(0);

			// populate children (does nothing if not a directory)
			getSubFileData(newFile, userID, depth);

			return newFile;
		}

		// recursively populate directories down to depth given
			// since this is private, it is assumed that all necessary validation has been done and file permissions are up-to-date
		//TODO?: exception handling??
		private static void getSubFileData(RRFile file, int userID, int depth)
		{
			// if not a directory or recursive base case, then do nothing
			if (!file.IsDir || depth < 1)
			{
				return;
			}
			// build MySQL query to get sub files
			string sqlStr = "SELECT f.*, " + (userID==RRUser.GuestUserID ? "NULL"							// if guest user, then don't look for user-specific permissions
												: file.UserPermission==RRPermissionType.admin ? "'admin'"	// elseif registered user has 'admin' permission on parent dir, then all children have 'admin' permission
																											// else, find registered user's permissions for children:
												: "(SELECT CASE WHEN EXISTS (SELECT * FROM permissions p WHERE p.userID=" + userID + " AND p.fileID=f.fileID)"														// if there is an explicit permission
																+ " THEN (SELECT pt.permName FROM permissions p, permissionTypes pt WHERE p.userID=" + userID + " AND p.fileID=f.fileID AND p.permID=pt.permID)"	// then get that permission
																+ " ELSE '" + file.UserPermission.ToString() + "' END)")																							// else, inherit permission from parent
											+ " AS userPermName, "
											+ (file.PublicPermission==RRPermissionType.none ? "'none'"		// if public has 'none' permission on parent dir, then all children have 'none' permission
												: file.PublicPermission==RRPermissionType.admin ? "'admin'"	// elseif public has 'admin' permission on parent dir, then all children have 'admin' permission (this is obviously not suggested, but allowable)
																											// else, find public's permissions for children:
												: "(SELECT CASE WHEN EXISTS (SELECT * FROM permissions p WHERE p.userID=(SELECT userID FROM users WHERE userName='guest') AND p.fileID=f.fileID)"													// if there is an explicit permission
																+ " THEN (SELECT pt.permName FROM permissions p, permissionTypes pt WHERE p.userID=(SELECT userID FROM users WHERE userName='guest') AND p.fileID=f.fileID AND p.permID=pt.permID)"	// then get that permission
																+ " ELSE '" + file.PublicPermission.ToString() + "' END)")																															// else, inherit permission from parent
											+ " AS publicPermName"
									+ " FROM files f WHERE f.fileID!=f.superDirID AND f.superDirID=" + file.FileID	// get all sub files (ignore root which is its own sub/super)
									+ " HAVING userPermName!='none' OR publicPermName!='none'";						// a file is only reachable if user has some permission
			// run query
			List<RRFile> res = new List<RRFile>(GetData(ExecuteReader(sqlStr), RRFile.construct));
			// add each result file to tree and recursively populate it
			for (int i = 0; i < res.Count; i++)
			{
				file.SubFiles.Add(res[i]);		// parent -> child
				res[i].SuperDir = file;			// child -> parent
				getSubFileData(res[i], userID, depth-1);
			}
		}

		/*
        #region public methods
        ///// <summary>
        ///// selects all projects from database
        ///// </summary>
        ///// <returns>all Projects in database</returns>
        //public Dictionary<string, Project> GetAllProjectData()
        //{
        //    string procedureText =
        //        "SELECT PROJECT_NAME, PROJECT_TYPE, PROJECT_OWNER  FROM PROJECTDATA NATURAL JOIN PROJECTOWNERS";

        //    //performs select and gets list of DALProjectData from database
        //    var result = GetData(ExecuteReader(CreateCommand(procedureText, CommandType.Text, null)),
        //                         CreateProjectData);

        //    return BuildProjectObject(new List<DALProjectData>(result));
        //}

        //public void DeleteProject(string projectName)
        //{
        //    var procedureText = String.Format("DELETE FROM PROJECTDATA WHERE PROJECT_NAME = '{0}';DELETE FROM PROJECTOWNERS WHERE PROJECT_NAME = '{0}';",
        //                                         projectName);
        //    var rowsAffected = ExecuteNonQuery(procedureText);
        //}

        public int DeleteUniversity(int univID)
        {
            var procedureText = String.Format("DELETE FROM university WHERE univID = '{0}';",
                                                 univID);
            return ExecuteNonQuery(procedureText);
        }

        public int AddUniversity(int univID, string univName)
        {
            var procedureText = String.Format("INSERT INTO university (univID, univName) VALUES ({0}, {1});", univID,
                                              univName);
            return ExecuteNonQuery(procedureText);
        }

        public Dictionary<int, University> GetAllUniversities()
        {
            string procedureText =
                "SELECT *  FROM university";

            //performs select and gets list of DALProjectData from database
            var result = GetData(ExecuteReader(CreateCommand(procedureText, CommandType.Text, null)),
                                 CreateUniversityData);

            return BuildUniversityDictionary(new List<University>(result));
        }

        public int AddDepartment(int deptID, int univID, string deptName)
        {
            var procedureText =
                String.Format("INSERT INTO universityDepartments (deptID, univID, deptName) VALUES ({0}, {1}, {2});",
                              deptID, univID, deptName);
            return ExecuteNonQuery(procedureText);
        }

        public int DeleteDepartment(int deptID)
        {
            var procedureText = String.Format("DELETE FROM universityDepartments WHERE deptID = '{0}';", deptID);
            return ExecuteNonQuery(procedureText);
        }

        public int AddUser(int userID, string userName, string userPass, int deptID, string firstName, string middleName, string lastName, string preTitles, string postTitles)
        {
            var procedureText =
                String.Format(
                    "INSERT INTO users (userID, userName, userPass, deptID, firstName, middleName, lastName, preTitles, postTitles) VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}');",
                    userID, userName, userPass, deptID, firstName, middleName, lastName, preTitles, postTitles);

            return ExecuteNonQuery(procedureText);
        }

        public int DeleteUser(int userID)
        {
            var procedureText = String.Format("DELETE FROM users WHERE userID = '{0}';", userID);
            return ExecuteNonQuery(procedureText);
        }

        public int AddFilePurposeType(int purpID)
        {
            var procedureText = String.Format("INSERT INTO filePurposeTypes (purpID) VALUES ({0});", purpID);
            return ExecuteNonQuery(procedureText);
        }

        public int DeleteFilePurposeType(int purpID)
        {
            var procedureText = String.Format("DELETE FROM filePurposeTypes WHERE purpID = '{0}';", purpID);
            return ExecuteNonQuery(procedureText);
        }

        public int AddFile(int fileID, int superDirID, string fileName, Boolean isDir, DateTime dateCreated, int creatorID, DateTime dateModified, int modifierID)
        {
            var procedureText =
                String.Format(
                    "INSERT INTO files (fileID, superDirID, fileName, isDir, dateCreated, creatorID, dateModified, modifierID) VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7});",
                    fileID, superDirID, fileName, isDir, dateCreated, creatorID, dateModified, modifierID);
            return ExecuteNonQuery(procedureText);
        }

        public int DeleteFile(int fileID)
        {
            var procedureText = String.Format("DELETE FROM files WHERE fileID = '{0}';");
            return ExecuteNonQuery(procedureText);
        }

        public int AddDiscipline(int discID, int superDiscID, string discName)
        {
            var procedureText =
                String.Format("INSERT INTO disciplines (discID, superDiscID, discName) VALUES ({0}, {1}, {2});", discID,
                              superDiscID, discName);
            return ExecuteNonQuery(procedureText);
        }

        public int DeleteDiscipline(int discID)
        {
            var procedureText = String.Format("DELETE FROM disciplines WHERE discID = '{0}';", discID);
            return ExecuteNonQuery(procedureText);
        }

        public int AddResearchType(int resID, int superResID, string resName)
        {
            var procedureText =
                String.Format("INSERT INTO researchTypes (resID, superResID, resName) VALUES ({0}, {1}, {2});", resID,
                              superResID, resName);
            return ExecuteNonQuery(procedureText);
        }

        public int DeleteResearchType(int resID)
        {
            var procedureText = String.Format("DELETE FROM researchTypes WHERE resID = '{0}';", resID);
            return ExecuteNonQuery(procedureText);
        }

        public int AddProject(int projID, string projName, int rootDirID, int discID, int resID)
        {
            var procedureText =
                String.Format(
                    "INSERT INTO projects (projID, projName, rootDirID, discID, resID) VALUES ({0}, {1}, {2}, {3}, {4});",
                    projID, projName, rootDirID, discID, resID);
            return ExecuteNonQuery(procedureText);
        }

        public int DeleteProject(int projID)
        {
            var procedureText = String.Format("DELETE FROM projects WHERE projID = '{0}';", projID);
            return ExecuteNonQuery(procedureText);
        }
        #endregion

        #region private methods
        ///// <summary>
        ///// Creates dictionary with Project objects
        ///// </summary>
        ///// <param name="projectDataList">a list of DALProjectDatas to be converted to Project objects</param>
        ///// <returns>dictionary of Projects keyed by Project Name</returns>
        //private static Dictionary<string, Project> BuildProjectObject(IEnumerable<DALProjectData> projectDataList)
        //{
        //    var projectDataDictionary = new Dictionary<string, Project>();

        //    //iterate through DALProjectDatas and set Project properties to equivalent fields
        //    //add Projects to dictionary
        //    foreach (var projectData in projectDataList)
        //    {
        //        Project project;

        //        project = new Project
        //        {
        //            ProjectName = projectData.ProjectName,
        //            ProjectOwner = projectData.ProjectOwner,
        //            ProjectType = projectData.ProjectType
        //        };

        //        projectDataDictionary.Add(project.ProjectName, project);

        //    }

        //    return projectDataDictionary;
        //}

        private static Dictionary<int, University> BuildUniversityDictionary(IEnumerable<University> universityList)
        {
            var universityDictionary = new Dictionary<int, University>();

            foreach (var universityData in universityList)
            {
                University university;

                university = new University
                                 {
                                     UnivId = universityData.UnivId,
                                     UnivName = universityData.UnivName
                                 };
                universityDictionary.Add(university.UnivId, university);
            }

            return universityDictionary;
        }

        private static University CreateUniversityData(IDataRecord record)
        {
            return new University
                       {
                           UnivId = (int) record["univID"],
                           UnivName = record["univID"].ToString()
                       };
        }
        ///// <summary>
        ///// Gets properties from a single record from database, sets to properties of DALProjectData
        ///// </summary>
        ///// <param name="record">single database row</param>
        ///// <returns>DALProjectData representaiton of row</returns>
        //private static DALProjectData CreateProjectData(IDataRecord record)
        //{
        //    return new DALProjectData
        //    {
        //        ProjectName = record["PROJECT_NAME"].ToString(),
        //        ProjectOwner = record["PROJECT_OWNER"].ToString(),
        //        ProjectType = record["PROJECT_TYPE"].ToString()
        //    };
        //}

        /// <summary>
        /// Creates MySqlCommand
        /// </summary>
        /// <param name="command">command text</param>
        /// <param name="commandType">command type</param>
        /// <param name="mySqlParameters">command paramaters</param>
        /// <returns>complete MySqlCommand</returns>
        private MySqlCommand CreateCommand(string command, CommandType commandType, IEnumerable<MySqlParameter> mySqlParameters)
        {
            var mySqlCommand = new MySqlCommand(command, Connection);
            mySqlCommand.CommandType = commandType;

            if (mySqlParameters != null)
            {
                foreach (var param in mySqlParameters)
                {
                    mySqlCommand.Parameters.Add(param);
                }
            }

            return mySqlCommand;
        }

        /// <summary>
        /// Creates a reader for the given command
        /// </summary>
        /// <param name="command">command to be executed</param>
        /// <returns></returns>
        private static IDataReader ExecuteReader(IDbCommand command)
        {
            try
            {
                return command.ExecuteReader();
            }
            catch (InvalidOperationException invalidOperationException)
            {
                throw new Exception(invalidOperationException.Message, invalidOperationException.InnerException);
            }
            catch (MySqlException MyqlException)
            {
                throw new Exception(MyqlException.Message, MyqlException.InnerException);
            }
        }
		*/

        /// <summary>
        /// Takes an object type which uses a reader and build a class representation of the data
        /// </summary>
        /// <typeparam name="T">The type of object that represents that database data</typeparam>
        /// <param name="reader">The reader object</param>
        /// <param name="buildObject">The object that will be used to hold that reader data</param>
        /// <returns>An enumerable object to represent the reader data</returns>
        private static IEnumerable<T> GetData<T>(IDataReader reader, Func<IDataRecord, T> buildObject)
        {
            try
            {
                if (!reader.IsClosed)
                {
                    while (reader.Read())
                    {
                        yield return buildObject(reader);
                    }
                }
            }
            finally
            {
				reader.Dispose();
				conn.Close();
            }
        }

		/*
        //TODO: error handling
        /// <summary>
        /// performs nonqueries on db (inserts/deletes)
        /// </summary>
        /// <param name="command"></param>
        /// <returns>number of rows affected</returns>
        private int ExecuteNonQuery(string commandText)
        {
            var command = CreateCommand(commandText, CommandType.Text, null);

            return command.ExecuteNonQuery();
        }

        #endregion
		*/
    }
}